////////////////////////////////////////////////////////////////////////////////
//  COPYRIGHT (C) 2010 TELECOM DIGITAL MEDIA ("TDMEDIA").
//  ALL RIGHTS RESERVED.
//
//  THIS IS CONFIDENTIAL AND PROPRIETARY INTELLECTUAL PROPERTY OWNED BY AND
//  CREATED ON BEHALF OF TDMEDIA. ANY FORM OF DISTRIBUTION, COPY,
//  MODIFICATION WITHOUT THE WRITTEN CONSENT FROM TDMEDIA IS STRICTLY
//  PROHIBITED.
////////////////////////////////////////////////////////////////////////////////
package cc.telecomdigital.tdstock;

import java.util.List;
import java.util.Vector;

import cc.telecomdigital.tdstock.Framework.UniqueItem.TDStockLog;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * for operating the sqlite
 * 
 * 
 * @author haibo
 *
 */

public class DBHelper
{

	public static final String		TAG					= DBHelper.class
															.getSimpleName();
	public static final String		DB_NAME				= "f_quotedev";
	// public static final String DB_TABLE = "f_teletext_portfolios";
	public static final String		DB_COLUMN				= "stockcode";
	public static final int			DB_VERSION			= 11;

	public static final String		DB_TABLE_CUSTOMERINFO	= "f_customerinfo";
	public static final String		DB_TABLE_SUBSCRIPTION	= "f_subscriptioninfo";
	public static final String		DB_TABLE_STOCK_DIARY	= "f_stockdiary";
	public static final String		DB_TABLE_PREFERENCE		= "f_preference";
	
	
	public static final int        LIMIT_NUMBER_ON_SUBSCRIPTION = 50;
	public static final int        LIMIT_NUMBER_ON_SUBSCRIPTION_BMP_STOCKCOUNT = 20;

	private static final String		CLASSNAME				= DBHelper.class
															.getSimpleName();
	//  private static final String[] COLS = new String[] {DB_COLUMN};

	private SQLiteDatabase			db;
	private final DBOpenHelper		dbOpenHelper;
	private static SharedPreferences	mSharedPreferences;

	private static class DBOpenHelper extends SQLiteOpenHelper
	{

		private static final String	DB_TABLE_CUSTOMERINFO	= "CREATE TABLE "
															+ DBHelper.DB_TABLE_CUSTOMERINFO
															+ "( customerid  INTEGER PRIMARY KEY AUTOINCREMENT,"
															+ " deviceid TEXT,"
															+ " loginname TEXT UNIQUE,"
															+ "loginpin TEXT,"
															+ "primaryemail TEXT,"
															+ "active BOOLEAN "
															+ ")";

		private static final String	DB_TABLE_SUBSCRIPTION	= "CREATE TABLE "
															+ DBHelper.DB_TABLE_SUBSCRIPTION
															+ "(  customerid  INTEGER, "
															+ "  item_name TEXT,"
															+ "   item_index INTEGER,"
															+ "  item_adapter TEXT,"
															+ "   field_name TEXT,"
															+ "  field_value NUMERIC(10,5),"
															+ "   field_net NUMERIC(10,5),"
															+ "  field_netpercentage NUMERIC(10,5),"
															+ "  PRIMARY KEY(customerid, item_name, item_adapter)  "
															+ ")";

		private static final String	DB_TABLE_STOCKDIARY		= "CREATE TABLE "
															+ DBHelper.DB_TABLE_STOCK_DIARY
															+ "(  recno  INTEGER PRIMARY KEY AUTOINCREMENT,"
															+ " customerid  INTEGER,"
															+ "   stockcode TEXT,"
															+ "  input_date DATE,"
															+ "   lotsize INTEGER,"
															+ "  price  NUMERIC(10,5),"
															+ "  desc_content TEXT "
															+ ")";

		private static final String	DB_TABLE_PREFERENCE		= "CREATE TABLE "
															+ DBHelper.DB_TABLE_PREFERENCE
															+ " (customerid  INTEGER,"
															+ "   catalogue TEXT,"
															+ " current_value TEXT,"
															+ "  desc_content TEXT, "
															+ "  PRIMARY KEY(customerid, catalogue)  "
															+ ")";

		public DBOpenHelper(final Context context)
		{
			super(context, DBHelper.DB_NAME, null, DBHelper.DB_VERSION);
			mSharedPreferences = context.getSharedPreferences(
					CommonDefn.SYSTEM_CURRENT_SETTING,
					Context.MODE_PRIVATE);
		}

		@Override
		public void onCreate(final SQLiteDatabase db)
		{
			try
			{
				db.execSQL(DBOpenHelper.DB_TABLE_CUSTOMERINFO);
				db.execSQL(DBOpenHelper.DB_TABLE_SUBSCRIPTION);
				db.execSQL(DBOpenHelper.DB_TABLE_STOCKDIARY);
				db.execSQL(DBOpenHelper.DB_TABLE_PREFERENCE);

			}
			catch (SQLException e)
			{
				TDStockLog.e(TAG, DBHelper.CLASSNAME, e);
			}
		}

		@Override
		public void onOpen(final SQLiteDatabase db)
		{
			super.onOpen(db);
		}

		@Override
		public void onUpgrade(final SQLiteDatabase db, final int oldVersion,
				final int newVersion)
		{
			if (oldVersion < newVersion)
			{
				//  db.execSQL("DROP TABLE IF EXISTS " + DBHelper.DB_TABLE);
				db.execSQL("DROP TABLE IF EXISTS "
						+ DBHelper.DB_TABLE_CUSTOMERINFO);
				db.execSQL("DROP TABLE IF EXISTS "
						+ DBHelper.DB_TABLE_SUBSCRIPTION);
				db.execSQL("DROP TABLE IF EXISTS "
						+ DBHelper.DB_TABLE_STOCK_DIARY);
				db.execSQL("DROP TABLE IF EXISTS "
						+ DBHelper.DB_TABLE_PREFERENCE);
				onCreate(db);
			}
			else
			{
				onCreate(db);
			}
		}
	}

	//
	// end inner classes
	//

	public DBHelper(final Context context)
	{
		this.dbOpenHelper = new DBOpenHelper(context);
		establishDb();
	}

	private void establishDb()
	{
		if (this.db == null)
		{
			this.db = this.dbOpenHelper.getWritableDatabase();
		}
	}

	public void cleanup()
	{
		if (this.db != null)
		{
			this.db.close();
			this.db = null;
		}
	}

	/* *//**
			* 
			* @param stockCode
			* subscribe the stock code
			*/
	/*
	public void insert(int  stockCode) {
	 ContentValues values = new ContentValues();
	 values.put(DB_COLUMN,stockCode);
	 if(this.db!= null && this.dbOpenHelper != null&& this.db.isOpen())
	 	this.db.insert(DBHelper.DB_TABLE, null, values);
	}*/

	public boolean updateSubscribeItems(int customerId, String[] items,
			String[] adapter, int[] index)
	{
		boolean result = false;
		try
		{
			if (this.db.inTransaction())
				this.db.endTransaction();
			this.db.beginTransaction();
			if (items.length == adapter.length
					&& adapter.length == index.length)
			{
				for (int item = 0; item < items.length; item++)
				{
					String tmpSql = null;
					if (index[item] <= 0)
					{
						tmpSql = String
								.format(
										"delete from %s where customerid = %d  and  item_name =\"%s\"  and  item_adapter =\"%s\" ",
										DBHelper.DB_TABLE_SUBSCRIPTION,
										customerId, items[item],
										adapter[item]);
					}
					else
					{
						tmpSql = String
								.format(
										"update %s set item_index = %d  where customerid = %d  and  item_name =\"%s\"  and  item_adapter =\"%s\" ",
										DBHelper.DB_TABLE_SUBSCRIPTION,
										index[item], customerId,
										items[item], adapter[item]);
					}
					db.execSQL(tmpSql);
				}

			}

			this.db.setTransactionSuccessful();
			result = true;
		}

		catch (Exception e)
		{
			result = false;
			if (this.db.inTransaction())
				this.db.endTransaction();
		}
		if (this.db.inTransaction())
			this.db.endTransaction();

		return result;
	}

	public boolean isSubscribeItemExist(int customerId, String item_name,
			String item_adapter)
	{
		boolean result = false;
		Cursor c = null;
		c = this.db
				.rawQuery(
						String
								.format(
										"select item_index  from %s where customerId=%d  and item_name=\"%s\" and item_adapter=\"%s\"",
										DBHelper.DB_TABLE_SUBSCRIPTION,
										customerId, item_name,
										item_adapter), null);
		result = (c != null && c.getCount() >= 1);
		c.close();;
		return result;
	}
	
	
	
	public boolean isFullWithItemInMonitor(int customerId)
	{
		boolean result = false;
		Cursor c = null;
		c = this.db
				.rawQuery(
						String
								.format(
										"select count(*)  as  items  from %s  where customerId=%d",
										DBHelper.DB_TABLE_SUBSCRIPTION,
										customerId), null);
		if(c!= null) 
			c.moveToLast();
		result = (c != null && c.getCount() == 1 && c.getInt(0) >= LIMIT_NUMBER_ON_SUBSCRIPTION);
		c.close();;
		return result;
	}
	
	public boolean isFullWithItemInMonitorBMPStock(int customerId)
	{
		List<String[]> items = getSubscribeItemsByUserId(customerId);
		int count = 0;
		for(int i = 0; i < items.size(); i++)
		{
			String[] arrays = items.get(i);
			try
			{
				if(Integer.parseInt(arrays[0]) > 0)
				{
					count++;
				}
			}
			catch(Exception e)
			{
			}
		}
		TDStockLog.d(TAG, "count="+count);
		if(count >= LIMIT_NUMBER_ON_SUBSCRIPTION_BMP_STOCKCOUNT) return true;
		else return false;
	}
	
	public long insertSubscribeItem(int customerId, String item_name,
			String item_adapter, String field_name, String field_value,
			String field_net, String field_netpercentage)
	{
		long result = -1l;
		try
		{
			if (this.db.inTransaction())
				this.db.endTransaction();
			this.db.beginTransaction();

			ContentValues values = new ContentValues();
			values.put("customerid", customerId);
			values.put("item_name", item_name);
			values.put("item_adapter", item_adapter);
			values.put("field_name", field_name);
			values.put("field_value", field_value);
			values.put("field_net", field_net);
			values.put("field_netpercentage", field_netpercentage);

			Cursor c = null;
			/*     c = this.db.query(true, DBHelper.DB_TABLE_SUBSCRIPTION, "max(item_index)",  DB_COLUMN + " ="  + code , null, null, null, null,
			              null);*/
			c = this.db
					.rawQuery(
							String
									.format(
											"select max(item_index)  from %s where customerId=%d ",
											DBHelper.DB_TABLE_SUBSCRIPTION,
											customerId), null);
			if (c != null && c.getCount() >= 1)
			{
				c.moveToFirst();
				int index = c.isNull(0) ? 0 : c.getInt(0);
				values.put("item_index", index + 1);
			}
			else
			{
				values.put("item_index", 1);
			}
			if (c != null)
			{
				c.close();
			}

			if (this.db != null && this.dbOpenHelper != null
					&& this.db.isOpen())
				result = this.db.insert(DBHelper.DB_TABLE_SUBSCRIPTION,
						null, values);
			this.db.setTransactionSuccessful();
		}

		catch (Exception e)
		{
			result = -1l;
			if (this.db.inTransaction())
				this.db.endTransaction();
		}
		if (this.db.inTransaction())
			this.db.endTransaction();

		return result;
	}

	public List<String[]> getDiaryItemsByItemCode(int userId, String itemCode)
	{
		final List<String[]> vector = new Vector<String[]>();
		Cursor c = null;
		c = this.db.query(true, DBHelper.DB_TABLE_STOCK_DIARY,
				new String[] { "recno", "input_date", "lotsize", "price",
						"desc_content" }, "customerid=" + userId
						+ "  and  stockcode= "
						+ StringUtils.quoStr(itemCode), null, null, null,
				" input_date  desc, recno desc", null);
		if (c != null && c.getCount() >= 1)
		{
			while (c.moveToNext())
			{
				String[] tmp = new String[5];
				for (int index = 0; index < tmp.length; index++)
				{
					tmp[index] = c.getString(index);
				}
				vector.add(tmp);
			}
		}
		if (c != null)
		{
			c.close();
		}
		return vector;
	}

	public long insertOrUpdateStockDiary(int recNo, int customerId,
			String stockCode, String input_date, int lotsize, double price,
			String desc)
	{
		long result = -1l;
		ContentValues values = new ContentValues();
		values.put("customerid", customerId);
		values.put("stockcode", stockCode);
		values.put("input_date", input_date);
		values.put("lotsize", lotsize);
		values.put("price", price);
		values.put("desc_content", desc == null ? "" : desc);

		if (this.db != null && this.dbOpenHelper != null && this.db.isOpen())
		{
			if (recNo <= 0)
			{ //ready to insert into table
				TDStockLog.d(DBHelper.TAG,
						"ready to insert a record  to stock diary");
				result = this.db.insert(DBHelper.DB_TABLE_STOCK_DIARY,
						null, values);
				TDStockLog.d(DBHelper.TAG,
						"have inserted  a record  to stock diary   and the recno is "
								+ result);
			}
			else
			{
				TDStockLog.d(DBHelper.TAG,
						"ready to upate a record  to stock diary which recno is "
								+ recNo);
				result = this.db.update(DBHelper.DB_TABLE_STOCK_DIARY,
						values, "recno=" + recNo, null);
			}

		}

		return result;
	}

	public void deleteStockDiary(int recNo)
	{
		this.db.delete(DBHelper.DB_TABLE_STOCK_DIARY, "recno=" + recNo, null);
	}

	public String getPreference(int customerId, String catalogue)
	{
		String result = null;
		if (this.db.isOpen())
		{
			Cursor c = null;
			c = this.db.query(true, DBHelper.DB_TABLE_PREFERENCE,
					new String[] { "current_value" }, "customerid ="
							+ customerId + " and  catalogue="
							+ StringUtils.quoStr(catalogue), null, null,
					null, null, null);
			if (c != null && c.getCount() >= 1)
			{
				c.moveToPosition(0);
				result = c.getString(0);
			}
			if (c != null)
			{
				c.close();
			}
		}

		return result;
	}

	public String insertOrUpdatePreference(int customerId, String catalogue,
			String preferenceValue, boolean isLogin)
	{
		String result = null;
		try
		{
			this.db.beginTransaction();
			String preferenc_value = getPreference(customerId, catalogue);
			if (preferenc_value == null)
			{
				//insert a record
				ContentValues values = new ContentValues();
				values.put("customerid", customerId);
				values.put("catalogue", catalogue);
				if (catalogue
						.equals(CommonDefn.DB_CHART_PREFER_RSITERM_CATALOGUE))
				{
					result = "14";
					values.put("current_value", result);
					values.put("desc_content", "RSI计算天数,默认值: 14");
				}
				if (this.db != null && this.dbOpenHelper != null
						&& this.db.isOpen())
				{
					TDStockLog.d(DBHelper.TAG,
							"ready to insert a record  to f_preference!  "
									+ catalogue);
					if (this.db.insert(DBHelper.DB_TABLE_PREFERENCE, null,
							values) < 0l)
					{
						result = null;
					}
				}

			}
			else
			{
				if (isLogin)
				{
					return preferenc_value;
				}

				ContentValues values = new ContentValues();
				values.put("current_value", preferenceValue == null
						|| preferenceValue.length() < 1 ? "14"
						: preferenceValue);

				if (this.db != null && this.dbOpenHelper != null
						&& this.db.isOpen())
				{
					TDStockLog.d(DBHelper.TAG,
									"ready to upate  the record  to f_preference! ");
					if (this.db.update(DBHelper.DB_TABLE_PREFERENCE,
							values, "customerid=" + customerId
									+ "   and  catalogue="
									+ StringUtils.quoStr(catalogue),
							null) <= 0)
					{
						result = null;
					}
					else
					{
						result = preferenceValue == null
								|| preferenceValue.length() < 1 ? "14"
								: preferenceValue;
					}
				}

			}
			this.db.setTransactionSuccessful();

		}
		catch (Exception e)
		{
			result = null;
			this.db.endTransaction();
		}
		if (this.db.inTransaction())
		{
			this.db.endTransaction();
		}

		return result;
	}

	/***
	 * @param userName
	 * @param pwd
	 * @param deviceid
	 */
	public long insertOrUpdateUser(String userName, String pwd, String deviceid)
	{
		long result = -1l;
		try
		{
			this.db.beginTransaction();
			result = getUserIdByName(userName);
			if (result == -1)
			{
				//insert a record
				ContentValues values = new ContentValues();
				values.put("deviceid", deviceid);
				values.put("loginname", userName);
				values.put("loginpin", pwd);
				values.put("active", 1);
				if (this.db != null && this.dbOpenHelper != null
						&& this.db.isOpen())
				{
					TDStockLog.d(DBHelper.TAG,
							"ready to insert a record and the deviceid is "
									+ (deviceid == null ? "null"
											: deviceid));
					result = this.db
							.insert(DBHelper.DB_TABLE_CUSTOMERINFO,
									null, values);
				}
				/*if(mSharedPreferences != null &&  !CommonDefn.ACCOUNT_USER_NAME_TRIAL_DELAY.equals(userName)) {
					  mSharedPreferences.edit()
					.putBoolean(CommonDefn.PREFERENCE_IS_FIRST_LOGON_APP, true)
					.commit();
				}*/
			}
			else
			{
				ContentValues values = new ContentValues();
				values.put("deviceid", deviceid);
				values.put("loginpin", pwd);
				values.put("active", 1);
				if (this.db != null && this.dbOpenHelper != null
						&& this.db.isOpen())
				{
					TDStockLog.d(DBHelper.TAG,
							"ready to upate  the record and the deviceid is "
									+ deviceid);
					this.db.update(DBHelper.DB_TABLE_CUSTOMERINFO, values,
							"loginname=" + StringUtils.quoStr(userName),
							null);
				}

			}
			this.db.setTransactionSuccessful();

		}
		catch (Exception e)
		{
			this.db.endTransaction();
		}
		if (this.db.inTransaction())
		{
			this.db.endTransaction();
		}

		return result;
	}

	public int getUserIdByName(String userName)
	{
		int result = -1;

		if (this.db.isOpen())
		{
			Cursor c = null;
			c = this.db.query(true, DBHelper.DB_TABLE_CUSTOMERINFO,
					new String[] { "customerid" }, "loginname="
							+ StringUtils.quoStr(userName), null, null,
					null, null, null);
			if (c != null && c.getCount() >= 1)
			{
				c.moveToPosition(0);
				result = c.getInt(0);
			}
			if (c != null)
			{
				c.close();
			}
		}

		return result;
	}

	public List<String[]> getSubscribeItemsByUserId(int userId)
	{
		final List<String[]> vector = new Vector<String[]>();
		Cursor c = null;
		try
		{
			c = this.db.query(true, DBHelper.DB_TABLE_SUBSCRIPTION,
					new String[] { "item_name", "item_adapter",
							"field_name", "field_value", "field_net",
							"field_netpercentage" }, "customerid="
							+ userId, null, null, null,
					" item_index asc", null);
			if (c != null && c.getCount() >= 1)
			{
				while (c.moveToNext())
				{
					String[] tmp = new String[6];
					for (int index = 0; index < tmp.length; index++)
					{
						tmp[index] = c.getString(index);
					}
					vector.add(tmp);
				}
			}
			if (c != null)
			{
				c.close();
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return vector;
	}
}
